検証: リストアのエラーパターン
from SQL Serverのバックアップ・リストア仕様https://gyazo.com/b6a00b7958a23c4329e79f83c5d31b3f
概要・環境
SQL Serverのリストアのパターン
C. 元のデータベースが存在するサーバーに、新しいデータベース名で以前のディスクバックアップを復元する
バックアップのタイムスケジュールは以下の通り
リストアテスト用のバックアップタイムスケジュール
リストア手順は概ねこれに基づく
SSMSからSQL Serverのデータベースをリストアする
設定項目のテンプレ: データベースの復元#64e6b5bcf4582000005d682d
確認方法は概ねこれに基づく
リストア履歴をシステムデータベースから確認するSQL
確認したいシチュエーション
色々なオプションをつけたり外したりしてリストアを失敗させ、止まったときは復旧させたい
パターンA: 既存のデータベースを上書きするをチェックしないとき、エラーが起こることを確認したい
完全2.0を使う
ref: 検証: リストアのエラーパターン#64e44d08f458200000c3aab8
パターンB: ソース データベースを復元中の状態にしておくをチェックしたとき、データベースを復旧させたい
完全2.0を使う
ref: 検証: リストアのエラーパターン#64e44d6ff458200000c3aad5
パターンC: 完全(Copy Only)の復元の仕方の確認
完コピを使う
ref: 検証: リストアのエラーパターン#64ed587af458200000628d52
パターンD: 完全(Copy Only)と差分バックアップ、トランザクションログバックアップを使って最新状態まで復旧させたい
完コピとログを使う(ref先参照)
ref: 検証: リストアのエラーパターン#64ed8183f458200000628d8c
パターンA
使うバックアップ
完全2.0
準備
新データベースtest_4を作成し、データベースの復元
設定
既存のデータベースを上書きするのチェックなし
復元の前にログ末尾のバックアップを実行するのチェックを外す
結果
「上書きする」チェックをしていないのでエラーになる
データベース 'sample' の復元に失敗しました。
わかる
パターンB
使うバックアップ
完全2.0
準備
新データベースtest_5を作成し、データベースの復元
設定
既存のデータベースを上書きするをチェックする
復元の前にログ末尾のバックアップを実行するをチェックしたまま
ソース データベースを復元中の状態にしておくをチェックしたまま
ダイアログに以下の警告
ソース データベースのログ末尾のバックアップが実行されます。この設定は オプション ページに表示されます。
結果
table:restore情報
date dest filegroup restore_type device_name physical replace recovery restart stop_at stop_before
... test_5 PRIMARY データベース 08_20_0000~.bak test_5.mdf 指定 recovery なし NULL NULL
... test_5 PRIMARY データベース 08_20_0000~.bak test_5_1.ldf 指定 recovery なし NULL NULL
restorehistoryのrecovery列が1(recovery)で追加されている
バックアップ「元」のデータベースが復元しています…になって読み書きできなくなってしまう
例えば本番環境のバックアップファイルから復元すると本番環境が止まってしまう
データベース 'sample' を開けません。復元中です。
sys.databasesからデータベースの状態を確認するとバックアップ元のstateがRESTORINGになっている
table:result(抜粋)
name state_desc ...
master ONLINE ...
本番環境 RESTORING ...
test_5 ONLINE ...
とりあえず復旧するには復元しています…を解消するSQL
どういう状態になるのかわかっていない
パターンC
使うバックアップ
完コピ
準備
新データベースtest_9を作成し、データベースの復元
設定
既存のデータベースを上書きするをチェック
復元の前にログ末尾のバックアップを実行するのチェックを外す
結果
成功する
パターンD
使うバックアップ
後述
準備
新データベースtest_10を作成し、データベースの復元
設定
既存のデータベースを上書きするをチェック
復元の前にログ末尾のバックアップを実行するのチェックを外す
試行錯誤
試行錯誤その1
はじめに、SSMSでバックアップファイルを選択するとき以下のようにした
完コピ
差分3.2
ログ3.2.0～ログ3.2.23
しかしこれではファイルがすべて選択されなかった(完コピが表示されない)
試行錯誤その2
次に、完コピを適用してから差分とログを適用しようと考えた
以下のみ選択
完コピ
復元成功した
続けて以下の差分とログを選択し復元したらエラーが出た
差分3.2とログ3.2.0～ログ3.2.23
System.Data.SqlClient.SqlError: バックアップ セットは、既存のデータベース 'sample' 以外のデータベースのバックアップを保持しています。 (Microsoft.SqlServer.SmoExtended)
設定で復旧状態がRESTORE WITH RECOVERYをしていたので、そのまま完コピを復元するとRECOVERY状態で復元完了してしまうので、差分とログが適用できない
それはそう
試行錯誤その3
完コピの復元を以下の設定でやりなおした
既存のデータベースを上書きするをチェック
復旧状態をRESTORE WITH NORECOVERY
復元の前にログ末尾のバックアップを実行するのチェックを外す
復元成功した
もう一度以下の差分とログを選択し復元したらまたエラーが出た
差分3.2とログ3.2.0～ログ3.2.23
System.Data.SqlClient.SqlError: この差分バックアップを復元できません。データベースが以前の正しい状態に復元されていません。 (Microsoft.SqlServer.SmoExtended)
なんでだろう？
あ、ログチェーンが繋がっていないのにくっつけようとしたからだ
試行錯誤その4(正解)
こう選択すれば完全(Copy Only)から復元できた
完コピ
ログ3.1.16～ログ3.2.23
なんで…
ログチェーンを数字で見るとまったく頭が回らないけど、図で考えたらわかった
code:mmd
gantt
title Copy Onlyと差分バックアップは繋がっていない
dateFormat YYYY-MM-DD HH:mm
axisFormat %m/%d(%a)
section 完全バックアップ
完全バックアップ : done , milestone , full02 , 2023/8/20 0:00 , 0
Copy_Only(朝9時) : active , crit, milestone , copyonly01 , 2023/8/23 09:00 , 0
section 差分バックアップ
差分_月曜 : done , diff07 , after full02 , 1d
差分_火曜 : done , diff08 , after full02 , 2d
差分_水曜 : done , diff09 , after full02 , 3d
差分_木曜 : diff10 , after full02 , 4d
差分_金曜 : diff11 , after full02 , 5d
section ログバックアップ
...... : done , logryaku02 , 2023/8/20 0:00 , 78.5h
ログ_23日6時 : done , trn2306 , 2023/8/23 6:30 , 0.5h
ログ_23日7時 : done , trn2307 , 2023/8/23 7:30 , 0.5h
ログ_23日8時 : done , trn2308 , 2023/8/23 8:30 , 0.5h
ログ_23日9時 : active , crit , trn2309 , 2023/8/23 9:30 , 0.5h
ログ_23日10時 : active , crit , trn2310 , 2023/8/23 10:30 , 0.5h
ログ_23日11時 : active , crit , trn2311 , 2023/8/23 11:30 , 0.5h
ログ_23日12時 : active , crit , trn2312 , 2023/8/23 12:30 , 0.5h
ログ_23日13時 : active , crit , trn2313 , 2023/8/23 13:30 , 0.5h
ログ_23日14時 : active , crit , trn2314 , 2023/8/23 14:30 , 0.5h
ログ_23日15時 : active , crit , trn2315 , 2023/8/23 15:30 , 0.5h
...... : active , crit , logryaku03 , 2023/8/23 16:00 , 32h
完全(Copy Only)、差分、ログって繋がるわけないわ
自分で「Copy Onlyはベースを変えずにバックアップをとる」って書いているのに実際に直面するまで忘れてた